﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class AppParam
    {
        public AppParam()
        { 
        }

        public List<AppParamInfo> GetList()
        {
            List<AppParamInfo> list = new List<AppParamInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 ROW_ID,GU_FUP,GU_CLOSE_DEAL,PURGING_PERIOD,PURGING_PERIOD_WEB,WEB_ROOT_ADDRESS,DB_PASS_NEW,
                DB_PASS_OLD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,PROCESS_DATE,NPWP,TIME_OUT,IS_AUTO_PROCESS_DATE,REFRESH_TIME,JOB_FINAL_DEDUP,IS_NEED_FINAL_DEDUP
            FROM TBL_SYS_PARAMS ORDER BY UPDATE_DATE DESC");
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AppParamInfo(dr.GetByte(0), dr.GetByte(1), dr.GetByte(2), dr.GetByte(3), dr.GetByte(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetDateTime(11), dr.GetDateTime(12), dr.GetDouble(13), dr.GetByte(14), dr.GetString(15), dr.GetByte(16), dr.GetByte(17), dr.GetString(18)));
                }
                dr.Close();
            }
            return list;
        }

        public List<AppParamInfo> GetListApproval(string UpdateBy)
        {
            List<AppParamInfo> list = new List<AppParamInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ROW_ID,GU_FUP,GU_CLOSE_DEAL,PURGING_PERIOD,PURGING_PERIOD_WEB,WEB_ROOT_ADDRESS,DB_PASS_NEW,
                                DB_PASS_OLD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE,PROCESS_DATE,NPWP,TIME_OUT,IS_AUTO_PROCESS_DATE,REFRESH_TIME,JOB_FINAL_DEDUP,IS_NEED_FINAL_DEDUP, 
                                GU_FUP_FUNDING,GU_CLOSE_DEAL_FUNDING
            FROM TBL_SYS_PARAMS_TMP WHERE (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AppParamInfo(dr.GetByte(0), dr.GetByte(1), dr.GetByte(2), dr.GetByte(3), dr.GetByte(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetDateTime(11), dr.GetString(12), dr.GetDateTime(13), dr.GetDouble(14), dr.GetByte(15), dr.GetString(16), dr.GetByte(17), dr.GetByte(18), dr.GetString(19),
                        byte.Parse(dr["GU_FUP_FUNDING"].ToString()), byte.Parse(dr["GU_CLOSE_DEAL_FUNDING"].ToString())));
                }
                dr.Close();
            }
            return list;
        }

        public AppParamInfo Get()
        {
            AppParamInfo ci = new AppParamInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 ROW_ID,GU_FUP,GU_CLOSE_DEAL,PURGING_PERIOD,PURGING_PERIOD_WEB,WEB_ROOT_ADDRESS,DB_PASS_NEW,
                                DB_PASS_OLD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,PROCESS_DATE,NPWP,TIME_OUT,IS_AUTO_PROCESS_DATE,REFRESH_TIME,JOB_FINAL_DEDUP,IS_NEED_FINAL_DEDUP,
                                GU_FUP_FUNDING,GU_CLOSE_DEAL_FUNDING
                                FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new AppParamInfo(dr.GetByte(0), dr.GetByte(1), dr.GetByte(2), dr.GetByte(3), dr.GetByte(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetDateTime(11), dr.GetDateTime(12), dr.GetDouble(13), dr.GetByte(14), dr.GetString(15), dr.GetByte(16), dr.GetByte(17), dr.GetString(18),
                        byte.Parse(dr["GU_FUP_FUNDING"].ToString()), byte.Parse(dr["GU_CLOSE_DEAL_FUNDING"].ToString()));
                }
                dr.Close();
            }
            return ci;
        }

        public DateTime GetDateDb()
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT GETDATE()");
            return Convert.ToDateTime(db.ExecuteScalar(cmd));
        }

        public AppParamInfo GetApprovalByID(byte id)
        {
            AppParamInfo ci = new AppParamInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ROW_ID,GU_FUP,GU_CLOSE_DEAL,PURGING_PERIOD,PURGING_PERIOD_WEB,WEB_ROOT_ADDRESS,DB_PASS_NEW,
                                DB_PASS_OLD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE,PROCESS_DATE,NPWP,TIME_OUT,IS_AUTO_PROCESS_DATE,REFRESH_TIME,JOB_FINAL_DEDUP,IS_NEED_FINAL_DEDUP,
                                GU_FUP_FUNDING,GU_CLOSE_DEAL_FUNDING
                                FROM TBL_SYS_PARAMS_TMP WHERE ROW_ID = @ROW_ID");
            db.AddInParameter(cmd, "@ROW_ID", DbType.Byte, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new AppParamInfo(dr.GetByte(0), dr.GetByte(1), dr.GetByte(2), dr.GetByte(3), dr.GetByte(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetDateTime(11), dr.GetString(12), dr.GetDateTime(13), dr.GetDouble(14), dr.GetByte(15), dr.GetString(16), dr.GetByte(17), dr.GetByte(18), dr.GetString(19)
                         ,byte.Parse(dr["GU_FUP_FUNDING"].ToString()), byte.Parse(dr["GU_CLOSE_DEAL_FUNDING"].ToString()));
                }
                dr.Close();
            }
            return ci;
        }

        public void Save(AppParamInfo TBL_SYS_PARAMS)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_SYS_PARAMS_TMP SET 
                                GU_FUP = @GU_FUP
                                    ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL
                                ,GU_FUP_FUNDING = @GU_FUP_FUNDING
                                    ,GU_CLOSE_DEAL_FUNDING = @GU_CLOSE_DEAL_FUNDING
                                    ,PURGING_PERIOD = @PURGING_PERIOD
                                    ,PURGING_PERIOD_WEB = @PURGING_PERIOD_WEB
                                    ,WEB_ROOT_ADDRESS = @WEB_ROOT_ADDRESS
                                    ,DB_PASS_NEW = @DB_PASS_NEW
                                    ,DB_PASS_OLD = @DB_PASS_OLD
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,ACT_TYPE = @ACT_TYPE
                                    ,NPWP = @NPWP
                                    ,TIME_OUT = @TIME_OUT
                                    ,PROCESS_DATE = @PROCESS_DATE
                                    ,IS_AUTO_PROCESS_DATE = @IS_AUTO_PROCESS_DATE
                                    ,REFRESH_TIME = @REFRESH_TIME
                                    ,JOB_FINAL_DEDUP = @JOB_FINAL_DEDUP
                                    ,IS_NEED_FINAL_DEDUP = @IS_NEED_FINAL_DEDUP
                                WHERE (ROW_ID = @ROW_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_SYS_PARAMS_TMP(ROW_ID
                                    ,GU_FUP
                                    ,GU_CLOSE_DEAL
                                    ,GU_FUP_FUNDING
                                    ,GU_CLOSE_DEAL_FUNDING
                                    ,PURGING_PERIOD
                                    ,PURGING_PERIOD_WEB
                                    ,WEB_ROOT_ADDRESS
                                    ,DB_PASS_NEW
                                    ,DB_PASS_OLD
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,ACT_TYPE
                                    ,NPWP
                                    ,TIME_OUT
                                    ,PROCESS_DATE
                                    ,IS_AUTO_PROCESS_DATE
                                    ,REFRESH_TIME
                                    ,JOB_FINAL_DEDUP
                                    ,IS_NEED_FINAL_DEDUP
                                ) 
                                VALUES(@ROW_ID
                                    ,@GU_FUP
                                    ,@GU_CLOSE_DEAL
                                    ,@GU_FUP_FUNDING
                                    ,@GU_CLOSE_DEAL_FUNDING
                                    ,@PURGING_PERIOD
                                    ,@PURGING_PERIOD_WEB
                                    ,@WEB_ROOT_ADDRESS
                                    ,@DB_PASS_NEW
                                    ,@DB_PASS_OLD
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@ACT_TYPE
                                    ,@NPWP
                                    ,@TIME_OUT
                                    ,@PROCESS_DATE
                                    ,@IS_AUTO_PROCESS_DATE
                                    ,@REFRESH_TIME
                                    ,@JOB_FINAL_DEDUP
                                    ,@IS_NEED_FINAL_DEDUP
                                );
                            ");
            db.AddInParameter(cmd, "@ROW_ID", DbType.Byte, TBL_SYS_PARAMS.ROW_ID);
            db.AddInParameter(cmd, "@GU_FUP", DbType.Byte, TBL_SYS_PARAMS.GU_FUP);
            db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.Byte, TBL_SYS_PARAMS.GU_CLOSE_DEAL);
            db.AddInParameter(cmd, "@GU_FUP_FUNDING", DbType.Byte, TBL_SYS_PARAMS.GU_FUP_FUNDING);
            db.AddInParameter(cmd, "@GU_CLOSE_DEAL_FUNDING", DbType.Byte, TBL_SYS_PARAMS.GU_CLOSE_DEAL_FUNDING);
            db.AddInParameter(cmd, "@PURGING_PERIOD", DbType.Byte, TBL_SYS_PARAMS.PURGING_PERIOD);
            db.AddInParameter(cmd, "@PURGING_PERIOD_WEB", DbType.Byte, TBL_SYS_PARAMS.PURGING_PERIOD_WEB);
            db.AddInParameter(cmd, "@WEB_ROOT_ADDRESS", DbType.String, TBL_SYS_PARAMS.WEB_ROOT_ADDRESS);
            db.AddInParameter(cmd, "@DB_PASS_NEW", DbType.String, TBL_SYS_PARAMS.DB_PASS_NEW);
            db.AddInParameter(cmd, "@DB_PASS_OLD", DbType.String, TBL_SYS_PARAMS.DB_PASS_OLD);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, TBL_SYS_PARAMS.UPDATE_BY);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, TBL_SYS_PARAMS.UPDATE_DATE);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, TBL_SYS_PARAMS.ACT_TYPE);
            db.AddInParameter(cmd, "@NPWP", DbType.Double, TBL_SYS_PARAMS.NPWP);
            db.AddInParameter(cmd, "@TIME_OUT", DbType.Int16, TBL_SYS_PARAMS.TIME_OUT);
            db.AddInParameter(cmd, "@PROCESS_DATE", DbType.DateTime, TBL_SYS_PARAMS.PROCESS_DATE);
            db.AddInParameter(cmd, "@IS_AUTO_PROCESS_DATE", DbType.String, TBL_SYS_PARAMS.IS_AUTO_PROCESS_DATE);
            db.AddInParameter(cmd, "@REFRESH_TIME", DbType.Int16, TBL_SYS_PARAMS.REFRESH_TIME);
            db.AddInParameter(cmd, "@JOB_FINAL_DEDUP", DbType.Int16, TBL_SYS_PARAMS.JOB_FINAL_DEDUP);
            db.AddInParameter(cmd, "@IS_NEED_FINAL_DEDUP", DbType.String, TBL_SYS_PARAMS.IS_NEED_FINAL_DEDUP);
            db.ExecuteNonQuery(cmd);
        }

        public static void Approve(byte id, string DATABASE, int MINUTES)
        {            
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetStoredProcCommand("SYS_PARAMS_APPROVAL");
            db.AddInParameter(cmd, "@ROW_ID", DbType.Byte, id);
            db.ExecuteNonQuery(cmd);

            cmd = db.GetStoredProcCommand("CREATE_JOB_FINAL_DEDUP");
            db.AddInParameter(cmd, "@DATABASE", DbType.String, DATABASE);
            db.AddInParameter(cmd, "@MINUTES", DbType.Int32, MINUTES);
            db.ExecuteNonQuery(cmd);
        }

        public static void Reject(byte id)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = "DELETE FROM TBL_SYS_PARAMS_TMP WHERE ROW_ID = " + id;
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.ExecuteNonQuery(cmd);
        }
    }
 }